package com.cross.jdbc;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.List;

import org.dom4j.DocumentException;

import com.cross.core.AbstractWriter;
import com.cross.core.Column;
import com.cross.core.Header;
import com.cross.core.Item;
import com.cross.util.ItemType;

/**
 * jdbc写入器
 * 
 * @author www
 * 
 */
public class SimpleJdbcWriter extends AbstractWriter {
	// 数据表名
	protected String table;
	// 预处理语句
	protected PreparedStatement preparedStatement;
	// 数据库连接
	private Connection con;
	
	public SimpleJdbcWriter(){};
	/**
	 * 构造方法
	 * @param table
	 * @param header
	 * @throws FileNotFoundException
	 * @throws DocumentException
	 * @throws SQLException
	 */
	public SimpleJdbcWriter(Connection con, Header header)  throws SQLException {
		this.con = con;
		this.setHeader(header);
		table = header.getObjectName();
		if(table != null){
			preparedStatement = con.prepareStatement(getPreparedStatement(header));
		} else {
			throw new RuntimeException("table name is null, can't create prepared statement");
		}
	}
	
	/**
	 * 写入一行
	 */
	@Override
	protected void writeRow() {
		try {
			writeRowImpl();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 写入一行
	 * 
	 * @throws SQLException
	 */
	protected void writeRowImpl() throws SQLException {
		String type ;
		int parameterIndex = 0;
		
		if(table == null || !table.equals(header.getObjectName())){
			table = header.getObjectName();
			preparedStatement = con.prepareStatement(getPreparedStatement(header));
		}
		
		for(Column c : header.getColumns()){
			type = c.getType();
			if(ItemType.TYPE_STRING.equalsIgnoreCase(type)){
				preparedStatement.setString(parameterIndex+1, currentRow.getItem(parameterIndex).getString());
			} else if(ItemType.TYPE_DATE.equalsIgnoreCase(type)){
				preparedStatement.setObject(parameterIndex+1, currentRow.getItem(parameterIndex).get());
			} else if(ItemType.TYPE_TIME.equalsIgnoreCase(type)){
				preparedStatement.setTime(parameterIndex+1, currentRow.getItem(parameterIndex).getTime());
			} else if(ItemType.TYPE_TIMESTAMP.equalsIgnoreCase(type)){
				Timestamp timestamp = currentRow.getItem(parameterIndex).getTimestamp();
				if(timestamp == null){
					preparedStatement.setNull(parameterIndex+1, Types.TIMESTAMP);
				} else {
					preparedStatement.setTimestamp(parameterIndex+1, timestamp);
				}
			} else if(ItemType.TYPE_SHORT.equalsIgnoreCase(type)){
				preparedStatement.setShort(parameterIndex+1, currentRow.getItem(parameterIndex).getShort());
			} else if(ItemType.TYPE_FLOAT.equalsIgnoreCase(type)){
				preparedStatement.setFloat(parameterIndex+1, currentRow.getItem(parameterIndex).getFloat());
			} else if(ItemType.TYPE_LONG.equalsIgnoreCase(type)){
				preparedStatement.setLong(parameterIndex+1, currentRow.getItem(parameterIndex).getLong());
			} else if(ItemType.TYPE_INTEGER.equalsIgnoreCase(type)){
				preparedStatement.setInt(parameterIndex+1, currentRow.getItem(parameterIndex).getInteger());
			} else if(ItemType.TYPE_DOUBLE.equalsIgnoreCase(type)){
				preparedStatement.setDouble(parameterIndex+1, currentRow.getItem(parameterIndex).getDouble());
			} else if(ItemType.TYPE_NUMBER.equalsIgnoreCase(type)){
				Number number = currentRow.getItem(parameterIndex).getNumber();
				if(number == null){
					preparedStatement.setNull(parameterIndex+1, Types.NUMERIC);
				} else {
					preparedStatement.setDouble(parameterIndex+1, currentRow.getItem(parameterIndex).getNumber().doubleValue());
				}
			} else if(ItemType.TYPE_BOOLEAN.equalsIgnoreCase(type)){
				Item item = null;
				Boolean b = null;
				item = currentRow.getItem(parameterIndex);
				b = item.getBoolean();
				if(b == null){
					preparedStatement.setNull(parameterIndex+1, Types.BOOLEAN);
				} else {
					preparedStatement.setBoolean(parameterIndex+1, b);
				}
			} else if(ItemType.TYPE_BYTE.equalsIgnoreCase(type)){
				Byte b = currentRow.getItem(parameterIndex).getByte();
				if(b == null){
					preparedStatement.setNull(parameterIndex+1, Types.CHAR);
				} else {
					preparedStatement.setByte(parameterIndex+1, b.byteValue());
				}
			} else if(ItemType.TYPE_CHARACTER.equalsIgnoreCase(type)){
				preparedStatement.setString(parameterIndex+1, currentRow.getItem(parameterIndex).getCharacter()+"");
			} else {
				preparedStatement.setObject(parameterIndex+1, currentRow.getItem(parameterIndex).get());
			}
			parameterIndex++;
		}
		preparedStatement.execute();
	}

	/**
	 * 关闭
	 */
	@Override
	public void close() {
		try {
			con.commit();
			if(preparedStatement !=null)
			preparedStatement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	/**
	 * 根据header获得一个预处理sql
	 * 
	 * @param header
	 * @return
	 */
	protected String getPreparedStatement(Header header) {
		List<Column> columns = header.getColumns();
		if(columns == null || columns.size()<1){
			throw new RuntimeException("count of the column is zero");
		}
		StringBuffer sql = new StringBuffer();
		StringBuffer temp = new StringBuffer();
		sql.append("insert into ").append(table).append(" ( ");
		temp.append(" (");
		for (Column c : columns) {
			sql.append(c.getTitle());
			sql.append(", ");
			temp.append("?, ");
		}
		sql.replace(sql.lastIndexOf(","), sql.length(), " ) values ");
		temp.replace(temp.lastIndexOf(","), temp.length(), " ) ");
		sql.append(temp);
		return sql.toString();
	}
	
	/**
	 * 根据header获得一个建表sql
	 * 
	 * @param header
	 * @return
	 */
	protected String getCreateStatement(Header header) {
		List<Column> columns = header.getColumns();
		if(columns == null || columns.size()<1){
			throw new RuntimeException("count of the column is zero");
		}
		StringBuffer sql = new StringBuffer();
		sql.append("Create TABLE ").append(table).append(" ( ");
		for (Column c : columns) {
			sql.append(c.getTitle()).append(" ");
			String type = c.getType();
			if(ItemType.TYPE_STRING.equalsIgnoreCase(type)){
				sql.append("VARCHAR(").append(c.getPrecision()).append(")");
			} else if(ItemType.TYPE_DATE.equalsIgnoreCase(type)){
				sql.append("DATETIME");
			} else if(ItemType.TYPE_TIME.equalsIgnoreCase(type)){
				sql.append("DATETIME");
			} else if(ItemType.TYPE_TIMESTAMP.equalsIgnoreCase(type)){
				sql.append("TIMESTAMP");
			} else if(ItemType.TYPE_SHORT.equalsIgnoreCase(type)){
				sql.append("SMALLINT");
			} else if(ItemType.TYPE_FLOAT.equalsIgnoreCase(type)){
				sql.append("FLOAT");
			} else if(ItemType.TYPE_LONG.equalsIgnoreCase(type)){
				sql.append("BIGINT");
			} else if(ItemType.TYPE_INTEGER.equalsIgnoreCase(type)){
				sql.append("INT");
			} else if(ItemType.TYPE_DOUBLE.equalsIgnoreCase(type)){
				sql.append("REAL");
			} else if(ItemType.TYPE_NUMBER.equalsIgnoreCase(type)){
				sql.append("NUMERIC");
			} else if(ItemType.TYPE_BOOLEAN.equalsIgnoreCase(type)){
				sql.append("CHAR");
			} else if(ItemType.TYPE_BYTE.equalsIgnoreCase(type)){
				sql.append("BIT");
			} else if(ItemType.TYPE_CHARACTER.equalsIgnoreCase(type)){
				sql.append("CHAR");
			} else {
				sql.append("VARCHAR");
			}
			sql.append(", ");
		}
		sql.replace(sql.lastIndexOf(","), sql.length(), ")");
		return sql.toString();
	}
	
	protected boolean tableExist(String table){
		try{
			con.createStatement().executeQuery("select * from " + table);
		}catch(Exception e){
			return false;
		}
		return true;
	}
}
